﻿Imports UtilPadrao
Public Class perTipoServico
    Inherits PersistenciaPadrao.AcessoBD

    ReadOnly Property selecionarTiposServico(ByVal bFiltrarInativos As Boolean) As DataTable

        Get
            Dim sSql As String
            sSql = "SELECT codTipoServico, DescTipoServico,"
            sSql &= "CASE servicoOrigem WHEN 1 THEN 'Transporte de Valores' WHEN 2 THEN 'Tesouraria' WHEN 10 THEN 'Administrativo' END as 'Origem'"
            sSql &= " FROM com_TipoServico"
            If bFiltrarInativos Then
                sSql &= " WHERE COALESCE(inativo,0)=0"
            End If
            sSql &= " ORDER BY descTipoServico"

            Return MyBase.executarConsulta(sSql, True)

        End Get

    End Property

    Public Function inserirTipoServico(ByVal sDescTipoServico As String,
                                       ByVal iOrigem As Int32,
                                       ByVal bPermiteLancamentoManual As Boolean,
                                       ByVal bPermiteLancamentoFrequente As Boolean,
                                       ByVal bInativo As Boolean) As Int32
        Dim sSql As String

        sSql = "INSERT INTO com_tipoServico" & Environment.NewLine &
               "           (descTipoServico" & Environment.NewLine &
               "            ,servicoOrigem" & Environment.NewLine &
               "            ,servicoFrequente" & Environment.NewLine &
               "            ,lancamentoManual" & Environment.NewLine &
               "            ,inativo" & Environment.NewLine &
               "            )" & Environment.NewLine &
               "     VALUES" & Environment.NewLine &
               "           (" & Environment.NewLine &
               "            @descTipoServico" & Environment.NewLine &
               "           ,@servicoOrigem" & Environment.NewLine &
               "           ,@servicoFrequente" & Environment.NewLine &
               "           ,@lancamentoManual" & Environment.NewLine &
               "           ,@inativo" & Environment.NewLine &
               "            ); SELECT SCOPE_IDENTITY()"

        MyBase.limparParametros()
        MyBase.inserirParametro("@descTipoServico", sDescTipoServico)
        MyBase.inserirParametro("@servicoOrigem", iOrigem)
        MyBase.inserirParametro("@servicoFrequente", bPermiteLancamentoFrequente)
        MyBase.inserirParametro("@lancamentoManual", bPermiteLancamentoManual)
        MyBase.inserirParametro("@inativo", bInativo)

        Return MyBase.executarConsultaCampo(sSql, True)

    End Function

    Public Sub editarTipoServico(ByVal iCodServico As Int32,
                                 ByVal sDescTipoServico As String,
                                 ByVal iOrigem As Int32,
                                 ByVal bPermiteLancamentoManual As Boolean,
                                 ByVal bPermiteLancamentoFrequente As Boolean,
                                 ByVal bInativo As Boolean)
        Dim sSql As String

        sSql = "UPDATE com_TipoServico SET" & Environment.NewLine &
               "           descTipoServico = @descricao" & Environment.NewLine &
               "           ,servicoOrigem=@servicoOrigem" & Environment.NewLine &
               "           ,servicoFrequente=@servicoFrequente" & Environment.NewLine &
               "           ,lancamentoManual=@lancamentoManual" & Environment.NewLine &
               "           ,inativo=@inativo" & Environment.NewLine &
               "WHERE codTipoServico=@codTipoServico"

        MyBase.limparParametros()
        MyBase.inserirParametro("@descricao", sDescTipoServico)
        MyBase.inserirParametro("@servicoOrigem", iOrigem)
        MyBase.inserirParametro("@servicoFrequente", bPermiteLancamentoFrequente)
        MyBase.inserirParametro("@lancamentoManual", bPermiteLancamentoManual)
        MyBase.inserirParametro("@inativo", bInativo)
        MyBase.inserirParametro("@codTipoServico", iCodServico)

        MyBase.executarAcao(sSql, True)

    End Sub

    Public Function verificarTipoServicoCadastrado(ByVal iCodTipoServico As Int32,
                                                   ByVal sDescricao As String) As Boolean
        Dim sSql As String

        sSql = "SELECT codTipoServico" & Environment.NewLine &
               " FROM com_TipoServico" & Environment.NewLine &
               "WHERE descTipoServico = @descricao " & Environment.NewLine
        If iCodTipoServico > 0 Then
            sSql &= "  AND codTipoServico<>@codTipoServico"
        End If

        MyBase.limparParametros()
        MyBase.inserirParametro("@descricao", sDescricao)
        If iCodTipoServico > 0 Then
            MyBase.inserirParametro("@codTipoServico", iCodTipoServico)
        End If

        Return MyBase.executarConsulta(sSql, True).Rows.Count = 0

    End Function

    Public Function selecionarDadosTiposServico(ByVal iCodTipoServico As Int32) As DataRow
        Dim sSql As String
        Dim dtbDados As DataTable

        sSql = "SELECT *" & Environment.NewLine &
                "  FROM com_TipoServico" & Environment.NewLine &
                " WHERE codTIpoServico=@codTipoServico"

        MyBase.limparParametros()
        MyBase.inserirParametro("@codTipoServico", iCodTipoServico)

        dtbDados = MyBase.executarConsulta(sSql, True)

        If dtbDados.Rows.Count > 0 Then
            Return dtbDados.Rows(0)
        Else
            Return Nothing
        End If

    End Function

    Public Sub excluirTipoServico(ByVal iCodTipoServico As Int32)
        Dim sSql As String

        sSql = "DELETE FROM com_TipoServico" & Environment.NewLine &
               " WHERE codTipoServico = @codTipoServico;"

        MyBase.limparParametros()
        MyBase.inserirParametro("@codTipoServico", iCodTipoServico)

        MyBase.executarAcao(sSql, True)

    End Sub

    Public Function retornaPermiteLancamentoFrequente(ByVal iCodTipoServico As Int32) As Boolean
        Dim sSql As String

        sSql = "SELECT servicoFrequente" & Environment.NewLine &
               "  FROM com_TipoServico" & Environment.NewLine &
               " WHERE codTipoServico = @codTipoServico;"

        MyBase.limparParametros()
        MyBase.inserirParametro("@codTipoServico", iCodTipoServico)

        Return conversao.toBoolean(MyBase.executarConsultaCampo(sSql, True))

    End Function
End Class
